"""
Case Type   : 兼容M*数据库datetime类型
Case Name   : datetime类型插入日期0值，不同输入格式验证
Create At   : 2024/5/21
Owner       : @zhanghuan96
Description :
    1、创建数据库
    2、设置参数不含no_zero_date，建表并插入日期含0或全部为0值
    3、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
History     :modified by zhanghuan96，2024.6.22，修改断言，适配最新代码
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class CompatibilityTest033(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.db_name = "db_datetime_0033"
        self.tb_name = "tb_datetime_0033"

    def test_datetime(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '-----step1:创建数据库 expect:成功-----'
        self.log.info(text)
        create_cmd = f"drop database if exists {self.db_name};" \
                     f"create database {self.db_name} dbcompatibility 'b';"
        msg = self.sh_primary.execut_db_sql(create_cmd)
        self.log.info(msg)
        self.assertIn('CREATE DATABASE', msg, '执行失败:' + text)

        text = '---step2:设置参数不含no_zero_date，建表并插入日期含0或全部为0值 ' \
               'expect:成功--'
        self.log.info(text)
        sql_cmd = f"set dolphin.sql_mode='sql_mode_strict," \
                  f"sql_mode_full_group,pipes_as_concat,ansi_quotes';" \
                  f"create  table {self.tb_name} (c1 datetime);" \
                  f"insert {self.tb_name} values('00000101000000');" \
                  f"insert {self.tb_name} values(00000101000000);" \
                  f"insert {self.tb_name} values(datetime'00000101000000');" \
                  f"insert {self.tb_name} values(00000101000000::datetime);" \
                  f"insert {self.tb_name} " \
                  f"values(cast(00000101000000 as datetime));" \
                  f"insert {self.tb_name} values('00000000000000');" \
                  f"insert {self.tb_name} values(00000000000000);" \
                  f"insert {self.tb_name} values(datetime'00000000000000');" \
                  f"insert {self.tb_name} values(00000000000000::datetime);" \
                  f"insert {self.tb_name} " \
                  f"values(cast(00000000000000 as datetime));" \
                  f"select * from {self.tb_name};" \
                  f"select hex(compress(hextoraw('DEADBEEF')));"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertIn('SET', msg, '执行失败:' + text)
        self.assertIn('CREATE TABLE', msg, '执行失败:' + text)
        self.assertEqual(10, msg.count('INSERT 0 1'), '执行失败:' + text)
        self.assertEqual(2, msg.count('0000-01-01 00:00:00'), '执行失败:' + text)
        self.assertEqual(3, msg.count('2000-01-01 00:00:00'), '执行失败:' + text)
        self.assertEqual(5, msg.count('0000-00-00 00:00:00'), '执行失败:' + text)

    def tearDown(self):
        text = '----step3:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};"
        msg = self.sh_primary.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(msg)
        self.assertIn('DROP DATABASE', msg, '执行失败:' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
